﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace Common
{
    public class SqlHelper
    {
        private const int MaxPool = 15;//最大连接数
        private const int MinPool = 3;//最小连接数
        private const bool Asyn_Process = true;//设置异步访问数据库
        private const bool Mars = true;
        private const int Conn_Timeout = 10;//设置连接等待时间
        private const int Conn_Lifetime = 30;//设置连接的生命周期
        private string sqlConnectionString = "";//连接字符串
        private SqlConnection SqlConn = null;//连接对象
        public bool connectionStatus = false;

        public SqlHelper()
        {
        }
        /// <summary>
        /// 根据链接字符串，初始化链接并检查是否能正常打开
        /// </summary>
        /// <param name="sqlConnString">链接字符串</param>
        public SqlHelper(string sqlConnString)
        {
            SqlConn = new SqlConnection(sqlConnString);
            try
            {
                SqlConn.Open();
                sqlConnectionString = sqlConnString;
                connectionStatus = true;
            }
            catch (Exception ex)
            {
                connectionStatus = false;
                throw ex;
            }
        }

        public SqlHelper(ModelLibrary.Instance instance)
        {
            string sqlConnString = "";
            sqlConnString = "server=" + instance.address;
            sqlConnString += instance.port == "" ? "" : "," + instance.port;
            sqlConnString += instance.instance == "" ? "" : "\\" + instance.instance;
            sqlConnString += ";user id=" + instance.loginName + ";pwd=" + instance.password;
            sqlConnString += instance.connectTimeout == "" ? "" : ";connect Timeout=" + instance.connectTimeout;
            SqlConn = new SqlConnection(sqlConnString);
            try
            {
                SqlConn.Open();
                sqlConnectionString = sqlConnString;
                connectionStatus = true;
            }
            catch (Exception ex)
            {
                connectionStatus = false;
                throw ex;
            }
            finally
            {
                SqlConn.Close();
            }
        }



        public DataTable TableReads(string SqlString)
        {
            if (SqlConn.State != System.Data.ConnectionState.Closed)
            {
                SqlConn.Close();
            }
            try
            {
                SqlConn.Open();
                SqlCommand cmd = new SqlCommand(SqlString, SqlConn);
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                return dt;
            }
            catch (Exception ex)
            {
                return null;
                throw ex;
            }
            finally
            {
                SqlConn.Close();
            }
        }

    }
}
